package com.bsoft.gol.editor.dao;


import ctd.persistence.annotation.DAOMethod;
import ctd.persistence.annotation.DAOParam;
import ctd.persistence.support.hibernate.template.AbstractHibernateStatelessResultAction;
import ctd.persistence.support.hibernate.template.HibernateSessionTemplate;
import ctd.persistence.support.hibernate.template.HibernateStatelessResultAction;
import ctd.util.converter.ConversionUtils;
import com.bsoft.gol.editor.editor.*;
import org.apache.commons.lang3.StringUtils;
import org.apache.xmlbeans.impl.xb.xsdschema.Public;
import org.hibernate.SQLQuery;
import org.hibernate.StatelessSession;
import org.hibernate.criterion.CriteriaSpecification;
import org.hibernate.transform.Transformers;
import com.bsoft.gol.editor.utils.CommonUtils;

import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Created by Sangs on 2020/4/3.
 */
public abstract class EditorUserInfoDAO extends ctd.persistence.support.hibernate.HibernateSupportDelegateDAO<EditorUserInfo> {

    public EditorUserInfoDAO() {
        this.setKeyField("userInfoId");
    }

    /**
     * 查询列表
     * @return
     * @param req
     */
    public  List<EditorUserInfo> queryEditorUserInfoList(final EditorUserInfo req) {
        HibernateStatelessResultAction<List<EditorUserInfo>> action = new AbstractHibernateStatelessResultAction<List<EditorUserInfo>>() {
            @Override
            public void execute(StatelessSession ss) throws Exception {
                StringBuffer hsql = new StringBuffer();

                hsql.append("select a.user_info_id userInfoId,\n" +
                        "a.questionnaire_id questionnaireId,\n" +
                        "b.questionnaire_name questionnaireName,\n" +
                        "a.user_id userId,\n" +
                        "a.user_name userName,\n" +
                        "a.phone_num phoneNum,\n" +
                        "a.write_time writeTime,\n" +
                        "a.investigator_id investigatorId,\n" +
                        "a.questionnaire_way questionnaireWay,\n" +
                        "a.investigator_name investigatorName,\n" +
                        "a.answer_state answerState,\n" +
                        "a.create_id createId,\n" +
                        "a.create_time createTime\n from editor_user_info a,editor_questionnaire b where a.questionnaire_id=b.questionnaire_id");
                if(req.getQuestionnaireId() != null && StringUtils.isNotEmpty(req.getQuestionnaireId())) {
                    hsql.append(" and a.questionnaire_id='"+req.getQuestionnaireId()+"'");
                }
                if( req.getUserId() != null && StringUtils.isNotEmpty( String.valueOf(req.getUserId()))) {
                    hsql.append(" and a.user_id='"+req.getUserId()+"'");
                }
                if(req.getBeginTime() != null){
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd 00:00:00");
                    Date date = req.getBeginTime();
                    String strDate= sdf.format(date);
                    hsql.append(" and a.write_time>='"+strDate+"'");
                }
                if(req.getEndTime() != null){
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd 23:59:59");
                    Date date = req.getEndTime();
                    String strDate= sdf.format(date);
                    hsql.append(" and a.write_time<='"+strDate+"'");
                }
                if(req.getOrgId() != null&& StringUtils.isNotEmpty(String.valueOf(req.getOrgId()))){
                    hsql.append(" and b.org_id='"+req.getOrgId()+"'");
                }
                if(req.getIfAbnormal() != null){
                    hsql.append(" and b.if_abnormal='"+req.getIfAbnormal()+"'");
                }
                if(req.getUserName() != null && StringUtils.isNotEmpty(req.getUserName())) {
                    hsql.append(" and a.user_name like '%"+req.getUserName()+"%'");
                }
                if(req.getPhoneNum() != null && StringUtils.isNotEmpty(req.getPhoneNum())) {
                    hsql.append(" and a.phone_num='"+req.getPhoneNum()+"'");
                }
                if(req.getInvestigatorName() != null && StringUtils.isNotEmpty(req.getInvestigatorName())) {
                    hsql.append(" and a.investigator_name like '%"+req.getInvestigatorName()+"%'");
                }
                if(req.getCreateId() != null && StringUtils.isNotEmpty(req.getCreateId())) {
                    hsql.append(" and a.create_id='"+req.getCreateId()+"'");
                }
                if(req.getUserInfoIdList() != null  && req.getUserInfoIdList().size()!=0) {
                    hsql.append(" and a.user_info_id in (:userInfoIdList)");
                }


                hsql.append("  order by write_time desc ");

                SQLQuery sqlQuery = ss.createSQLQuery(hsql.toString());
                if(req.getUserInfoIdList() != null  && req.getUserInfoIdList().size()!=0) {
                    sqlQuery.setParameterList("userInfoIdList", req.getUserInfoIdList());
                }
                //分页
                int first = req.getPageNo() > 0 ? (req.getPageNo() - 1) * req.getPageSize() : 0;
                sqlQuery.setFirstResult(first);
                sqlQuery.setMaxResults(req.getPageSize());
                sqlQuery.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);

                //List<EditorUserInfo> list = sqlQuery.list();
                List<Map> dbList = sqlQuery.list();
                List<EditorUserInfo> list = new ArrayList<>();
                for (Map<String, Object> data : dbList) {
                    EditorUserInfo bean = ConversionUtils.convert(data, EditorUserInfo.class);
                    list.add(bean);
                }
                setResult(list);

            }
        };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }



    public   Long queryEditorUserInfoCount(final EditorUserInfo req) {
        HibernateStatelessResultAction<Long> action = new AbstractHibernateStatelessResultAction<Long>() {
            @Override
            public void execute(StatelessSession ss) throws Exception {
                StringBuffer hsql = new StringBuffer();

                hsql.append("select count(1) countValue from editor_user_info a,editor_questionnaire b where a.questionnaire_id=b.questionnaire_id");
                if(req.getQuestionnaireId() != null && StringUtils.isNotEmpty(req.getQuestionnaireId())) {
                    hsql.append(" and a.questionnaire_id='"+req.getQuestionnaireId()+"'");
                }
                if( req.getUserId() != null && StringUtils.isNotEmpty( String.valueOf(req.getUserId()))) {
                    hsql.append(" and a.user_id='"+req.getUserId()+"'");
                }
                if(req.getBeginTime() != null){
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd 00:00:00");
                    Date date = req.getBeginTime();
                    String strDate= sdf.format(date);
                    hsql.append(" and a.write_time>='"+strDate+"'");
                }
                if(req.getEndTime() != null){
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd 23:59:59");
                    Date date = req.getEndTime();
                    String strDate= sdf.format(date);
                    hsql.append(" and a.write_time<='"+strDate+"'");
                }
                if(req.getOrgId() != null&& StringUtils.isNotEmpty(String.valueOf(req.getOrgId()))){
                    hsql.append(" and b.org_id='"+req.getOrgId()+"'");
                }
                if(req.getIfAbnormal() != null){
                    hsql.append(" and b.if_abnormal='"+req.getIfAbnormal()+"'");
                }
                if(req.getUserName() != null && StringUtils.isNotEmpty(req.getUserName())) {
                    hsql.append(" and a.user_name like '%"+req.getUserName()+"%'");
                }
                if(req.getPhoneNum() != null && StringUtils.isNotEmpty(req.getPhoneNum())) {
                    hsql.append(" and a.phone_num='"+req.getPhoneNum()+"'");
                }
                if(req.getInvestigatorName() != null && StringUtils.isNotEmpty(req.getInvestigatorName())) {
                    hsql.append(" and a.investigator_name like '%"+req.getInvestigatorName()+"%'");
                }
                if(req.getCreateId() != null && StringUtils.isNotEmpty(req.getCreateId())) {
                    hsql.append(" and a.create_id='"+req.getCreateId()+"'");
                }
                if(req.getUserInfoIdList() != null  && req.getUserInfoIdList().size()!=0) {
                    hsql.append(" and a.user_info_id in (:userInfoIdList)");
                }
                hsql.append("  order by write_time desc ");

                SQLQuery sqlQuery = ss.createSQLQuery(hsql.toString());
                sqlQuery = (SQLQuery) sqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
                if(req.getUserInfoIdList() != null  && req.getUserInfoIdList().size()!=0) {
                    sqlQuery.setParameterList("userInfoIdList", req.getUserInfoIdList());
                }
                //List<EditorUserInfo> list = sqlQuery.list();
                List<Map> dbList = sqlQuery.list();
                if (CommonUtils.isNotEmpty(dbList)) {
                    setResult(Long.valueOf(String.valueOf(dbList.get(0).get("countValue"))));
                } else {
                    setResult(0L);
                }
            }
        };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }



    @DAOMethod(sql = "from EditorUserInfo where questionnaireId  =:questionnaireId order by userInfoId desc ")
    public abstract List<EditorUserInfo> queryEditorUserInfoByQId(@DAOParam("questionnaireId") String questionnaireId);

    @DAOMethod(sql = "from EditorUserInfo where userId=:userId and questionnaireId  =:questionnaireId order by writeTime desc  ")
    public abstract List<EditorUserInfo> queryEditorUserInfoByUserId(@DAOParam("userId") String userId,@DAOParam("questionnaireId") String questionnaireId);

    @DAOMethod(sql = "update EditorUserInfo set ifAbnormal= 1 where userInfoId =:userInfoId ")
    public abstract void updateEditorAnswerIfAbnormal(@DAOParam("userInfoId") String userInfoId);

    public  List<String> queryEditorUserInfo (final EditorUserInfo req){
        HibernateStatelessResultAction<List<String>> action = new AbstractHibernateStatelessResultAction<List<String>>() {
            @Override
            public void execute(StatelessSession ss) throws Exception {
                StringBuffer hsql = new StringBuffer();

                hsql.append("select a.user_info_id\n" +

                        " from editor_answer a where ");
                if(CommonUtils.isNotNull(req.getAnswers())){
                    int i=0;
                    for(EditorAnswer answers:req.getAnswers()){
                        if(i==0){
                        hsql.append("(1=1");
                        }else{
                            hsql.append("or (1=1");
                        }
                        i++;
                        for(EditorAnswer answersList: answers.getAnswersList()){
                            if(answersList.getOptionType()==1||answersList.getOptionType()==2||answersList.getOptionType()==3||answersList.getOptionType()==4||answersList.getOptionType()==5){
                                hsql.append(" and a.title_id='"+answersList.getTitleId()+"'");
                                hsql.append(" and a.answer_content='"+answersList.getAnswerContent()+"'");
                            }else if(answersList.getOptionType()==7||answersList.getOptionType()==6){
                                hsql.append(" and a.title_id='"+answersList.getTitleId()+"'");
                                hsql.append(" and a.answer_content like '%"+answersList.getAnswerContent()+"%'");
                            }

                        }
                        hsql.append(")");
                    }
                }



                SQLQuery sqlQuery = ss.createSQLQuery(hsql.toString());
                sqlQuery = (SQLQuery) sqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
                List<Map> lsm = (List<Map>) sqlQuery.list();
                List<String> list = new ArrayList<>();
                if(null!=lsm&&lsm.size()>0){
                    for (Map map : lsm) {
                        list.add((String) map.get("user_info_id"));
                    }
                }
                setResult(list);

            }
        };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }


    public  List<String> queryEditorUserInfo1 (final EditorAnswer req,final List<String> userInfoIdList){
        HibernateStatelessResultAction<List<String>> action = new AbstractHibernateStatelessResultAction<List<String>>() {
            @Override
            public void execute(StatelessSession ss) throws Exception {
                StringBuffer hsql = new StringBuffer();

                hsql.append("select a.user_info_id\n" +

                        " from editor_answer a where ");

                            if(req.getOptionType()==1||req.getOptionType()==2||req.getOptionType()==3||req.getOptionType()==4||req.getOptionType()==5||req.getOptionType()==14){
                                hsql.append("  a.title_id='"+req.getTitleId()+"'");
                                hsql.append(" and a.answer_content='"+req.getAnswerContent()+"'");
                            }else if(req.getOptionType()==7||req.getOptionType()==6||req.getOptionType()==13||req.getOptionType()==15){
                                hsql.append("  a.title_id='"+req.getTitleId()+"'");
                                hsql.append(" and a.answer_content like '%"+req.getAnswerContent()+"%'");
                            }

                if(userInfoIdList != null  && userInfoIdList.size()!=0) {
                    hsql.append(" and a.user_info_id in (:userInfoIdList)");
                }






                SQLQuery sqlQuery = ss.createSQLQuery(hsql.toString());
                sqlQuery = (SQLQuery) sqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
                if(userInfoIdList != null  && userInfoIdList.size()!=0) {
                    sqlQuery.setParameterList("userInfoIdList", userInfoIdList);
                }
                List<Map> lsm = (List<Map>) sqlQuery.list();
                List<String> list = new ArrayList<>();
                if(null!=lsm&&lsm.size()>0){
                    for (Map map : lsm) {
                        list.add((String) map.get("user_info_id"));
                    }
                }
                setResult(list);

            }
        };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }


}